{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd,xlwings as xw "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数据导入到pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "### 1.打开对应的工作簿\n",
    "\n",
    "wb_tb=xw.Book(r'C:\\Users\\UUPT\\Desktop\\月度结账报表\\5.优优家政\\河南优优家政202406\\财务报表\\部门发生额及余额表 (1).xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "### 2.从工作簿中读取数据到dataframe\n",
    "tb=wb_tb.sheets('第一页').range('c7').current_region.options(pd.DataFrame,index=False,header=False).value\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>科目类别</td>\n",
       "      <td>科目编码</td>\n",
       "      <td>科目名称</td>\n",
       "      <td>期初余额</td>\n",
       "      <td>None</td>\n",
       "      <td>本期发生</td>\n",
       "      <td>None</td>\n",
       "      <td>期末余额</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>借方</td>\n",
       "      <td>贷方</td>\n",
       "      <td>借方</td>\n",
       "      <td>贷方</td>\n",
       "      <td>借方</td>\n",
       "      <td>贷方</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>资产</td>\n",
       "      <td>1002</td>\n",
       "      <td>银行存款</td>\n",
       "      <td>9249.5</td>\n",
       "      <td>None</td>\n",
       "      <td>336814.87</td>\n",
       "      <td>309772.91</td>\n",
       "      <td>36291.46</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>资产</td>\n",
       "      <td>100201</td>\n",
       "      <td>招商银行-0001</td>\n",
       "      <td>9249.5</td>\n",
       "      <td>None</td>\n",
       "      <td>336814.87</td>\n",
       "      <td>309772.91</td>\n",
       "      <td>36291.46</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>资产</td>\n",
       "      <td>1123</td>\n",
       "      <td>预付账款</td>\n",
       "      <td>9800.0</td>\n",
       "      <td>None</td>\n",
       "      <td>17882.93</td>\n",
       "      <td>24682.93</td>\n",
       "      <td>3000.0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      0        1           2       3     4          5          6         7  \\\n",
       "0  科目类别     科目编码        科目名称    期初余额  None       本期发生       None      期末余额   \n",
       "1  None     None        None      借方    贷方         借方         贷方        借方   \n",
       "2    资产     1002        银行存款  9249.5  None  336814.87  309772.91  36291.46   \n",
       "3    资产  　100201  　招商银行-0001  9249.5  None  336814.87  309772.91  36291.46   \n",
       "4    资产     1123        预付账款  9800.0  None   17882.93   24682.93    3000.0   \n",
       "\n",
       "      8  \n",
       "0  None  \n",
       "1    贷方  \n",
       "2  None  \n",
       "3  None  \n",
       "4  None  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "##  看一下导入的数据的形状。\n",
    "# 注意：商贸和商贸分的科目余额表的格式居然不同，回头统一成默认的吧。  都改成数量金额式就可以了。\n",
    "\n",
    "tb.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 用Pandas对数据进行处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>科目类别</td>\n",
       "      <td>科目编码</td>\n",
       "      <td>科目名称</td>\n",
       "      <td>期初余额</td>\n",
       "      <td>期初余额</td>\n",
       "      <td>本期发生</td>\n",
       "      <td>本期发生</td>\n",
       "      <td>期末余额</td>\n",
       "      <td>期末余额</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>借方</td>\n",
       "      <td>贷方</td>\n",
       "      <td>借方</td>\n",
       "      <td>贷方</td>\n",
       "      <td>借方</td>\n",
       "      <td>贷方</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>资产</td>\n",
       "      <td>1002</td>\n",
       "      <td>银行存款</td>\n",
       "      <td>9249.5</td>\n",
       "      <td>None</td>\n",
       "      <td>336814.87</td>\n",
       "      <td>309772.91</td>\n",
       "      <td>36291.46</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>资产</td>\n",
       "      <td>100201</td>\n",
       "      <td>招商银行-0001</td>\n",
       "      <td>9249.5</td>\n",
       "      <td>None</td>\n",
       "      <td>336814.87</td>\n",
       "      <td>309772.91</td>\n",
       "      <td>36291.46</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>资产</td>\n",
       "      <td>1123</td>\n",
       "      <td>预付账款</td>\n",
       "      <td>9800.0</td>\n",
       "      <td>None</td>\n",
       "      <td>17882.93</td>\n",
       "      <td>24682.93</td>\n",
       "      <td>3000.0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      0        1           2       3     4          5          6         7  \\\n",
       "0  科目类别     科目编码        科目名称    期初余额  期初余额       本期发生       本期发生      期末余额   \n",
       "1                                 借方    贷方         借方         贷方        借方   \n",
       "2    资产     1002        银行存款  9249.5  None  336814.87  309772.91  36291.46   \n",
       "3    资产  　100201  　招商银行-0001  9249.5  None  336814.87  309772.91  36291.46   \n",
       "4    资产     1123        预付账款  9800.0  None   17882.93   24682.93    3000.0   \n",
       "\n",
       "      8  \n",
       "0  期末余额  \n",
       "1    贷方  \n",
       "2  None  \n",
       "3  None  \n",
       "4  None  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tb1=tb\n",
    "\n",
    "### 1.处理表头中的缺失值。表头有两行，一行是名称，一行是借方贷方，需要分别处理。\n",
    "\n",
    "# 1.1 处理第二行\n",
    "\n",
    "tb1.loc[0]=tb.loc[0].fillna(method='ffill')  # 填充空值，向下填充\n",
    "\n",
    "# 1.2 处理第二行\n",
    "\n",
    "tb1.loc[1]=tb.loc[1].fillna('') #这一行的None 填充为空\n",
    "\n",
    "# 1.3 处理后的数据\n",
    "tb1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "tb2=tb1\n",
    "### 2.开始进行合并单元格表头的处理，将多重表头合并为一重表头\n",
    "\n",
    "\n",
    "# 2.1 提升第一行为标题\n",
    "tb2.columns=tb2.loc[0] #将第一行作为列名\n",
    "tb2=tb2[1:]  #删除第一行\n",
    "# 2.2 转置数据\n",
    "tb2=tb2.T.reset_index()\n",
    "# 2.3 将第一列和第二列合并\n",
    "tb2[1]=tb2[0].str.cat(tb2[1])  #将第一列和第二列合并\n",
    "tb2=tb2.drop([0],axis=1) #删除多出来的 第0列。\n",
    "# 2.4 将数据转置回来\n",
    "tb2=tb2.set_index(1).T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>1</th>\n",
       "      <th>科目类别</th>\n",
       "      <th>科目编码</th>\n",
       "      <th>科目名称</th>\n",
       "      <th>期初余额借方</th>\n",
       "      <th>期初余额贷方</th>\n",
       "      <th>本期发生借方</th>\n",
       "      <th>本期发生贷方</th>\n",
       "      <th>期末余额借方</th>\n",
       "      <th>期末余额贷方</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>资产</td>\n",
       "      <td>1002</td>\n",
       "      <td>银行存款</td>\n",
       "      <td>9249.5</td>\n",
       "      <td>None</td>\n",
       "      <td>336814.87</td>\n",
       "      <td>309772.91</td>\n",
       "      <td>36291.46</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>资产</td>\n",
       "      <td>100201</td>\n",
       "      <td>招商银行-0001</td>\n",
       "      <td>9249.5</td>\n",
       "      <td>None</td>\n",
       "      <td>336814.87</td>\n",
       "      <td>309772.91</td>\n",
       "      <td>36291.46</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>资产</td>\n",
       "      <td>1123</td>\n",
       "      <td>预付账款</td>\n",
       "      <td>9800.0</td>\n",
       "      <td>None</td>\n",
       "      <td>17882.93</td>\n",
       "      <td>24682.93</td>\n",
       "      <td>3000.0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>资产</td>\n",
       "      <td>112301</td>\n",
       "      <td>已付款待开票</td>\n",
       "      <td>9800.0</td>\n",
       "      <td>None</td>\n",
       "      <td>17882.93</td>\n",
       "      <td>24682.93</td>\n",
       "      <td>3000.0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>资产</td>\n",
       "      <td>1221</td>\n",
       "      <td>其他应收款</td>\n",
       "      <td>4769.79</td>\n",
       "      <td>None</td>\n",
       "      <td>17944.0</td>\n",
       "      <td>23660.0</td>\n",
       "      <td>None</td>\n",
       "      <td>946.21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "1 科目类别     科目编码        科目名称   期初余额借方 期初余额贷方     本期发生借方     本期发生贷方    期末余额借方  \\\n",
       "2   资产     1002        银行存款   9249.5   None  336814.87  309772.91  36291.46   \n",
       "3   资产  　100201  　招商银行-0001   9249.5   None  336814.87  309772.91  36291.46   \n",
       "4   资产     1123        预付账款   9800.0   None   17882.93   24682.93    3000.0   \n",
       "5   资产  　112301     　已付款待开票   9800.0   None   17882.93   24682.93    3000.0   \n",
       "6   资产     1221       其他应收款  4769.79   None    17944.0    23660.0      None   \n",
       "\n",
       "1  期末余额贷方  \n",
       "2    None  \n",
       "3    None  \n",
       "4    None  \n",
       "5    None  \n",
       "6  946.21  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tb2.head()  #看一下tb2,到tb2，都是通用的处理，资产负债表说明，部门收支表都能在tb2的基础上做，下面可以做 资产负债表说明 和 部门收支表了。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 制作部门收支表\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 1.筛选损益科目\n",
    "\n",
    "tb3=(\n",
    "    tb2[tb2['科目类别'].isin(['损益'])] #筛选损益科目\n",
    "    .fillna(0)\n",
    "    .assign(余额借方= lambda x: x['本期发生借方']-x['本期发生贷方'])\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>1</th>\n",
       "      <th>科目类别</th>\n",
       "      <th>科目编码</th>\n",
       "      <th>科目名称</th>\n",
       "      <th>期初余额借方</th>\n",
       "      <th>期初余额贷方</th>\n",
       "      <th>本期发生借方</th>\n",
       "      <th>本期发生贷方</th>\n",
       "      <th>期末余额借方</th>\n",
       "      <th>期末余额贷方</th>\n",
       "      <th>余额借方</th>\n",
       "      <th>部门</th>\n",
       "      <th>科目名称2</th>\n",
       "      <th>科目编码2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>134</th>\n",
       "      <td>损益</td>\n",
       "      <td>6401</td>\n",
       "      <td>主营业务成本</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>14882.93</td>\n",
       "      <td>14882.93</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>合计</td>\n",
       "      <td>主营业务成本</td>\n",
       "      <td>6401</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>135</th>\n",
       "      <td>损益</td>\n",
       "      <td>640101</td>\n",
       "      <td>代家政服务成本</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>14882.93</td>\n",
       "      <td>14882.93</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>合计</td>\n",
       "      <td>代家政服务成本</td>\n",
       "      <td>640101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136</th>\n",
       "      <td>损益</td>\n",
       "      <td>64010101</td>\n",
       "      <td>保洁服务成本</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>14882.93</td>\n",
       "      <td>14882.93</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>合计</td>\n",
       "      <td>保洁服务成本</td>\n",
       "      <td>64010101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>137</th>\n",
       "      <td>损益</td>\n",
       "      <td>0</td>\n",
       "      <td>[24] UU家政 [J0020] 漯河市亿隆洁净环保服务有限公司 [99] UU家政</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2475.06</td>\n",
       "      <td>2475.06</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[24] UU家政 [J0020] 漯河市亿隆洁净环保服务有限公司 [99] UU家政</td>\n",
       "      <td>保洁服务成本</td>\n",
       "      <td>64010101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>138</th>\n",
       "      <td>损益</td>\n",
       "      <td>0</td>\n",
       "      <td>[24] UU家政 [J0022] 岳阳市经济技术开发区安生家政服务部 [99] UU家政</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2000.00</td>\n",
       "      <td>2000.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[24] UU家政 [J0022] 岳阳市经济技术开发区安生家政服务部 [99] UU家政</td>\n",
       "      <td>保洁服务成本</td>\n",
       "      <td>64010101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>139</th>\n",
       "      <td>损益</td>\n",
       "      <td>0</td>\n",
       "      <td>[24] UU家政 [J0042] 安阳市文峰区宅立净家政服务中心（个体工商户） [9...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5859.51</td>\n",
       "      <td>5859.51</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[24] UU家政 [J0042] 安阳市文峰区宅立净家政服务中心（个体工商户） [99] ...</td>\n",
       "      <td>保洁服务成本</td>\n",
       "      <td>64010101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>140</th>\n",
       "      <td>损益</td>\n",
       "      <td>0</td>\n",
       "      <td>[24] UU家政 [J0045] 柳州市鱼峰区荣美家政服务中心 [99] UU家政</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2780.00</td>\n",
       "      <td>2780.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[24] UU家政 [J0045] 柳州市鱼峰区荣美家政服务中心 [99] UU家政</td>\n",
       "      <td>保洁服务成本</td>\n",
       "      <td>64010101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141</th>\n",
       "      <td>损益</td>\n",
       "      <td>0</td>\n",
       "      <td>[24] UU家政 [J0047] 海南鑫恒创贸易有限公司 [99] UU家政</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1092.93</td>\n",
       "      <td>1092.93</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[24] UU家政 [J0047] 海南鑫恒创贸易有限公司 [99] UU家政</td>\n",
       "      <td>保洁服务成本</td>\n",
       "      <td>64010101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>142</th>\n",
       "      <td>损益</td>\n",
       "      <td>0</td>\n",
       "      <td>[24] UU家政 [J0058] 平顶山市新华区净谊保洁服务中心 [99] UU家政</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>269.43</td>\n",
       "      <td>269.43</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[24] UU家政 [J0058] 平顶山市新华区净谊保洁服务中心 [99] UU家政</td>\n",
       "      <td>保洁服务成本</td>\n",
       "      <td>64010101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143</th>\n",
       "      <td>损益</td>\n",
       "      <td>0</td>\n",
       "      <td>[24] UU家政 [J0072] 重庆万融生活服务有限公司 [99] UU家政</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>406.00</td>\n",
       "      <td>406.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[24] UU家政 [J0072] 重庆万融生活服务有限公司 [99] UU家政</td>\n",
       "      <td>保洁服务成本</td>\n",
       "      <td>64010101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144</th>\n",
       "      <td>损益</td>\n",
       "      <td>6601</td>\n",
       "      <td>销售费用</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>286725.52</td>\n",
       "      <td>286725.52</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>合计</td>\n",
       "      <td>销售费用</td>\n",
       "      <td>6601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>145</th>\n",
       "      <td>损益</td>\n",
       "      <td>660108</td>\n",
       "      <td>工资</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>192898.27</td>\n",
       "      <td>192898.27</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>合计</td>\n",
       "      <td>工资</td>\n",
       "      <td>660108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146</th>\n",
       "      <td>损益</td>\n",
       "      <td>0</td>\n",
       "      <td>[01] UU家政全国运营中心 [99] UU家政</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>95828.55</td>\n",
       "      <td>95828.55</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[01] UU家政全国运营中心 [99] UU家政</td>\n",
       "      <td>工资</td>\n",
       "      <td>660108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>147</th>\n",
       "      <td>损益</td>\n",
       "      <td>0</td>\n",
       "      <td>[02] 家政郑州 [99] UU家政</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>82962.53</td>\n",
       "      <td>82962.53</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[02] 家政郑州 [99] UU家政</td>\n",
       "      <td>工资</td>\n",
       "      <td>660108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>148</th>\n",
       "      <td>损益</td>\n",
       "      <td>0</td>\n",
       "      <td>[03] 家政三嫂 [99] UU家政</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>14107.19</td>\n",
       "      <td>14107.19</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[03] 家政三嫂 [99] UU家政</td>\n",
       "      <td>工资</td>\n",
       "      <td>660108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149</th>\n",
       "      <td>损益</td>\n",
       "      <td>660109</td>\n",
       "      <td>社会保险费</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>45564.00</td>\n",
       "      <td>45564.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>合计</td>\n",
       "      <td>社会保险费</td>\n",
       "      <td>660109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150</th>\n",
       "      <td>损益</td>\n",
       "      <td>0</td>\n",
       "      <td>[01] UU家政全国运营中心 [99] UU家政</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>14020.00</td>\n",
       "      <td>14020.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[01] UU家政全国运营中心 [99] UU家政</td>\n",
       "      <td>社会保险费</td>\n",
       "      <td>660109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>151</th>\n",
       "      <td>损益</td>\n",
       "      <td>0</td>\n",
       "      <td>[02] 家政郑州 [99] UU家政</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>11536.00</td>\n",
       "      <td>11536.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[02] 家政郑州 [99] UU家政</td>\n",
       "      <td>社会保险费</td>\n",
       "      <td>660109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152</th>\n",
       "      <td>损益</td>\n",
       "      <td>0</td>\n",
       "      <td>[03] 家政三嫂 [99] UU家政</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5608.00</td>\n",
       "      <td>5608.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[03] 家政三嫂 [99] UU家政</td>\n",
       "      <td>社会保险费</td>\n",
       "      <td>660109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>153</th>\n",
       "      <td>损益</td>\n",
       "      <td>0</td>\n",
       "      <td>[24] UU家政 [99] UU家政</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>14400.00</td>\n",
       "      <td>14400.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[24] UU家政 [99] UU家政</td>\n",
       "      <td>社会保险费</td>\n",
       "      <td>660109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154</th>\n",
       "      <td>损益</td>\n",
       "      <td>660111</td>\n",
       "      <td>经济补偿金</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>38463.25</td>\n",
       "      <td>38463.25</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>合计</td>\n",
       "      <td>经济补偿金</td>\n",
       "      <td>660111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155</th>\n",
       "      <td>损益</td>\n",
       "      <td>0</td>\n",
       "      <td>[02] 家政郑州 [99] UU家政</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>38463.25</td>\n",
       "      <td>38463.25</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[02] 家政郑州 [99] UU家政</td>\n",
       "      <td>经济补偿金</td>\n",
       "      <td>660111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156</th>\n",
       "      <td>损益</td>\n",
       "      <td>660156</td>\n",
       "      <td>营销费用</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>9800.00</td>\n",
       "      <td>9800.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>合计</td>\n",
       "      <td>营销费用</td>\n",
       "      <td>660156</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157</th>\n",
       "      <td>损益</td>\n",
       "      <td>66015601</td>\n",
       "      <td>营销费用-用户新开营销费</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>9800.00</td>\n",
       "      <td>9800.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>合计</td>\n",
       "      <td>营销费用-用户新开营销费</td>\n",
       "      <td>66015601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158</th>\n",
       "      <td>损益</td>\n",
       "      <td>0</td>\n",
       "      <td>[24] UU家政 [99] UU家政</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>9800.00</td>\n",
       "      <td>9800.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[24] UU家政 [99] UU家政</td>\n",
       "      <td>营销费用-用户新开营销费</td>\n",
       "      <td>66015601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159</th>\n",
       "      <td>损益</td>\n",
       "      <td>6602</td>\n",
       "      <td>管理费用</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>-14204.00</td>\n",
       "      <td>-14204.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>合计</td>\n",
       "      <td>管理费用</td>\n",
       "      <td>6602</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160</th>\n",
       "      <td>损益</td>\n",
       "      <td>660209</td>\n",
       "      <td>社会保险费</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>-14400.00</td>\n",
       "      <td>-14400.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>合计</td>\n",
       "      <td>社会保险费</td>\n",
       "      <td>660209</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>161</th>\n",
       "      <td>损益</td>\n",
       "      <td>0</td>\n",
       "      <td>[24] UU家政 [99] UU家政</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>-14400.00</td>\n",
       "      <td>-14400.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[24] UU家政 [99] UU家政</td>\n",
       "      <td>社会保险费</td>\n",
       "      <td>660209</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>162</th>\n",
       "      <td>损益</td>\n",
       "      <td>660252</td>\n",
       "      <td>工会经费</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>196.00</td>\n",
       "      <td>196.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>合计</td>\n",
       "      <td>工会经费</td>\n",
       "      <td>660252</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>163</th>\n",
       "      <td>损益</td>\n",
       "      <td>0</td>\n",
       "      <td>[24] UU家政 [99] UU家政</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>196.00</td>\n",
       "      <td>196.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[24] UU家政 [99] UU家政</td>\n",
       "      <td>工会经费</td>\n",
       "      <td>660252</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>164</th>\n",
       "      <td>损益</td>\n",
       "      <td>6603</td>\n",
       "      <td>财务费用</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>242.74</td>\n",
       "      <td>242.74</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>合计</td>\n",
       "      <td>财务费用</td>\n",
       "      <td>6603</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>165</th>\n",
       "      <td>损益</td>\n",
       "      <td>660302</td>\n",
       "      <td>手续费用</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>257.60</td>\n",
       "      <td>257.60</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>合计</td>\n",
       "      <td>手续费用</td>\n",
       "      <td>660302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166</th>\n",
       "      <td>损益</td>\n",
       "      <td>0</td>\n",
       "      <td>[01] UU家政全国运营中心 [G0004] 财付通支付科技有限公司 [99] UU家政</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>-0.01</td>\n",
       "      <td>-0.01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[01] UU家政全国运营中心 [G0004] 财付通支付科技有限公司 [99] UU家政</td>\n",
       "      <td>手续费用</td>\n",
       "      <td>660302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>167</th>\n",
       "      <td>损益</td>\n",
       "      <td>0</td>\n",
       "      <td>[24] UU家政 [G0003] 招商银行股份有限公司郑州紫荆山路支行 [99] UU家政</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>257.61</td>\n",
       "      <td>257.61</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[24] UU家政 [G0003] 招商银行股份有限公司郑州紫荆山路支行 [99] UU家政</td>\n",
       "      <td>手续费用</td>\n",
       "      <td>660302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>168</th>\n",
       "      <td>损益</td>\n",
       "      <td>660306</td>\n",
       "      <td>利息收入</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>-14.86</td>\n",
       "      <td>-14.86</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>合计</td>\n",
       "      <td>利息收入</td>\n",
       "      <td>660306</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "1   科目类别        科目编码                                               科目名称  \\\n",
       "134   损益        6401                                             主营业务成本   \n",
       "135   损益     　640101                                           　代家政服务成本   \n",
       "136   损益  　　64010101                                           　　保洁服务成本   \n",
       "137   损益           0     　　 [24] UU家政 [J0020] 漯河市亿隆洁净环保服务有限公司 [99] UU家政   \n",
       "138   损益           0   　　 [24] UU家政 [J0022] 岳阳市经济技术开发区安生家政服务部 [99] UU家政   \n",
       "139   损益           0  　　 [24] UU家政 [J0042] 安阳市文峰区宅立净家政服务中心（个体工商户） [9...   \n",
       "140   损益           0      　　 [24] UU家政 [J0045] 柳州市鱼峰区荣美家政服务中心 [99] UU家政   \n",
       "141   损益           0         　　 [24] UU家政 [J0047] 海南鑫恒创贸易有限公司 [99] UU家政   \n",
       "142   损益           0     　　 [24] UU家政 [J0058] 平顶山市新华区净谊保洁服务中心 [99] UU家政   \n",
       "143   损益           0        　　 [24] UU家政 [J0072] 重庆万融生活服务有限公司 [99] UU家政   \n",
       "144   损益        6601                                               销售费用   \n",
       "145   损益     　660108                                                　工资   \n",
       "146   损益           0                        　 [01] UU家政全国运营中心 [99] UU家政   \n",
       "147   损益           0                              　 [02] 家政郑州 [99] UU家政   \n",
       "148   损益           0                              　 [03] 家政三嫂 [99] UU家政   \n",
       "149   损益     　660109                                             　社会保险费   \n",
       "150   损益           0                        　 [01] UU家政全国运营中心 [99] UU家政   \n",
       "151   损益           0                              　 [02] 家政郑州 [99] UU家政   \n",
       "152   损益           0                              　 [03] 家政三嫂 [99] UU家政   \n",
       "153   损益           0                              　 [24] UU家政 [99] UU家政   \n",
       "154   损益     　660111                                             　经济补偿金   \n",
       "155   损益           0                              　 [02] 家政郑州 [99] UU家政   \n",
       "156   损益     　660156                                              　营销费用   \n",
       "157   损益  　　66015601                                     　　营销费用-用户新开营销费   \n",
       "158   损益           0                             　　 [24] UU家政 [99] UU家政   \n",
       "159   损益        6602                                               管理费用   \n",
       "160   损益     　660209                                             　社会保险费   \n",
       "161   损益           0                              　 [24] UU家政 [99] UU家政   \n",
       "162   损益     　660252                                              　工会经费   \n",
       "163   损益           0                              　 [24] UU家政 [99] UU家政   \n",
       "164   损益        6603                                               财务费用   \n",
       "165   损益     　660302                                              　手续费用   \n",
       "166   损益           0    　 [01] UU家政全国运营中心 [G0004] 财付通支付科技有限公司 [99] UU家政   \n",
       "167   损益           0   　 [24] UU家政 [G0003] 招商银行股份有限公司郑州紫荆山路支行 [99] UU家政   \n",
       "168   损益     　660306                                              　利息收入   \n",
       "\n",
       "1    期初余额借方  期初余额贷方     本期发生借方     本期发生贷方  期末余额借方  期末余额贷方  余额借方  \\\n",
       "134       0       0   14882.93   14882.93       0       0   0.0   \n",
       "135       0       0   14882.93   14882.93       0       0   0.0   \n",
       "136       0       0   14882.93   14882.93       0       0   0.0   \n",
       "137       0       0    2475.06    2475.06       0       0   0.0   \n",
       "138       0       0    2000.00    2000.00       0       0   0.0   \n",
       "139       0       0    5859.51    5859.51       0       0   0.0   \n",
       "140       0       0    2780.00    2780.00       0       0   0.0   \n",
       "141       0       0    1092.93    1092.93       0       0   0.0   \n",
       "142       0       0     269.43     269.43       0       0   0.0   \n",
       "143       0       0     406.00     406.00       0       0   0.0   \n",
       "144       0       0  286725.52  286725.52       0       0   0.0   \n",
       "145       0       0  192898.27  192898.27       0       0   0.0   \n",
       "146       0       0   95828.55   95828.55       0       0   0.0   \n",
       "147       0       0   82962.53   82962.53       0       0   0.0   \n",
       "148       0       0   14107.19   14107.19       0       0   0.0   \n",
       "149       0       0   45564.00   45564.00       0       0   0.0   \n",
       "150       0       0   14020.00   14020.00       0       0   0.0   \n",
       "151       0       0   11536.00   11536.00       0       0   0.0   \n",
       "152       0       0    5608.00    5608.00       0       0   0.0   \n",
       "153       0       0   14400.00   14400.00       0       0   0.0   \n",
       "154       0       0   38463.25   38463.25       0       0   0.0   \n",
       "155       0       0   38463.25   38463.25       0       0   0.0   \n",
       "156       0       0    9800.00    9800.00       0       0   0.0   \n",
       "157       0       0    9800.00    9800.00       0       0   0.0   \n",
       "158       0       0    9800.00    9800.00       0       0   0.0   \n",
       "159       0       0  -14204.00  -14204.00       0       0   0.0   \n",
       "160       0       0  -14400.00  -14400.00       0       0   0.0   \n",
       "161       0       0  -14400.00  -14400.00       0       0   0.0   \n",
       "162       0       0     196.00     196.00       0       0   0.0   \n",
       "163       0       0     196.00     196.00       0       0   0.0   \n",
       "164       0       0     242.74     242.74       0       0   0.0   \n",
       "165       0       0     257.60     257.60       0       0   0.0   \n",
       "166       0       0      -0.01      -0.01       0       0   0.0   \n",
       "167       0       0     257.61     257.61       0       0   0.0   \n",
       "168       0       0     -14.86     -14.86       0       0   0.0   \n",
       "\n",
       "1                                                   部门           科目名称2  \\\n",
       "134                                                 合计          主营业务成本   \n",
       "135                                                 合计        　代家政服务成本   \n",
       "136                                                 合计        　　保洁服务成本   \n",
       "137        [24] UU家政 [J0020] 漯河市亿隆洁净环保服务有限公司 [99] UU家政        　　保洁服务成本   \n",
       "138      [24] UU家政 [J0022] 岳阳市经济技术开发区安生家政服务部 [99] UU家政        　　保洁服务成本   \n",
       "139  [24] UU家政 [J0042] 安阳市文峰区宅立净家政服务中心（个体工商户） [99] ...        　　保洁服务成本   \n",
       "140         [24] UU家政 [J0045] 柳州市鱼峰区荣美家政服务中心 [99] UU家政        　　保洁服务成本   \n",
       "141            [24] UU家政 [J0047] 海南鑫恒创贸易有限公司 [99] UU家政        　　保洁服务成本   \n",
       "142        [24] UU家政 [J0058] 平顶山市新华区净谊保洁服务中心 [99] UU家政        　　保洁服务成本   \n",
       "143           [24] UU家政 [J0072] 重庆万融生活服务有限公司 [99] UU家政        　　保洁服务成本   \n",
       "144                                                 合计            销售费用   \n",
       "145                                                 合计             　工资   \n",
       "146                          [01] UU家政全国运营中心 [99] UU家政             　工资   \n",
       "147                                [02] 家政郑州 [99] UU家政             　工资   \n",
       "148                                [03] 家政三嫂 [99] UU家政             　工资   \n",
       "149                                                 合计          　社会保险费   \n",
       "150                          [01] UU家政全国运营中心 [99] UU家政          　社会保险费   \n",
       "151                                [02] 家政郑州 [99] UU家政          　社会保险费   \n",
       "152                                [03] 家政三嫂 [99] UU家政          　社会保险费   \n",
       "153                                [24] UU家政 [99] UU家政          　社会保险费   \n",
       "154                                                 合计          　经济补偿金   \n",
       "155                                [02] 家政郑州 [99] UU家政          　经济补偿金   \n",
       "156                                                 合计           　营销费用   \n",
       "157                                                 合计  　　营销费用-用户新开营销费   \n",
       "158                                [24] UU家政 [99] UU家政  　　营销费用-用户新开营销费   \n",
       "159                                                 合计            管理费用   \n",
       "160                                                 合计          　社会保险费   \n",
       "161                                [24] UU家政 [99] UU家政          　社会保险费   \n",
       "162                                                 合计           　工会经费   \n",
       "163                                [24] UU家政 [99] UU家政           　工会经费   \n",
       "164                                                 合计            财务费用   \n",
       "165                                                 合计           　手续费用   \n",
       "166      [01] UU家政全国运营中心 [G0004] 财付通支付科技有限公司 [99] UU家政           　手续费用   \n",
       "167     [24] UU家政 [G0003] 招商银行股份有限公司郑州紫荆山路支行 [99] UU家政           　手续费用   \n",
       "168                                                 合计           　利息收入   \n",
       "\n",
       "1         科目编码2  \n",
       "134        6401  \n",
       "135     　640101  \n",
       "136  　　64010101  \n",
       "137  　　64010101  \n",
       "138  　　64010101  \n",
       "139  　　64010101  \n",
       "140  　　64010101  \n",
       "141  　　64010101  \n",
       "142  　　64010101  \n",
       "143  　　64010101  \n",
       "144        6601  \n",
       "145     　660108  \n",
       "146     　660108  \n",
       "147     　660108  \n",
       "148     　660108  \n",
       "149     　660109  \n",
       "150     　660109  \n",
       "151     　660109  \n",
       "152     　660109  \n",
       "153     　660109  \n",
       "154     　660111  \n",
       "155     　660111  \n",
       "156     　660156  \n",
       "157  　　66015601  \n",
       "158  　　66015601  \n",
       "159        6602  \n",
       "160     　660209  \n",
       "161     　660209  \n",
       "162     　660252  \n",
       "163     　660252  \n",
       "164        6603  \n",
       "165     　660302  \n",
       "166     　660302  \n",
       "167     　660302  \n",
       "168     　660306  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tb4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "\n",
    "\n",
    "# 生成一列部门列，将部门列与科目名称列从一列拆成了两列了，方便使用sumifs\n",
    "tb4=tb3\n",
    "tb4['部门']=tb4.apply(lambda row:row ['科目名称'] if row['科目编码']==0 else '合计',axis=1).str.strip()     #生成部门列，同时加上合计这个部门\n",
    "\n",
    "# 生成一列科目名称列\n",
    "tb4['科目名称2']=tb4.apply(lambda row:pd.NA if row['科目编码']==0 else row ['科目名称'],axis=1)\n",
    "\n",
    "tb4['科目名称2'].fillna(method='ffill',inplace=True)  # 填充缺失值#\n",
    "\n",
    "tb4['科目编码2']=tb4.apply(lambda row:pd.NA if row['科目编码']==0 else row ['科目编码'],axis=1)\n",
    "\n",
    "tb4['科目编码2'].fillna(method='ffill',inplace=True)  # 填充缺失值#\n",
    "\n",
    "#wb_tb.sheets('数据源').range('A1').value=tb\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "tb4.to_clipboard()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "序号=tb4['科目编码'].drop_duplicates().reset_index(drop=True).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "序号.to_clipboard()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "tb5=pd.merge(tb4,序号,left_on=['科目编码2'],right_on=['科目编码'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "tb5.to_clipboard()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "xw.view(tb5.pivot(index=['index','科目编码2','科目名称2'],columns='部门',values='本期发生借方'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "ename": "com_error",
     "evalue": "(-2147352567, '发生意外。', (0, None, None, None, 0, -2147352565), None)",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mcom_error\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m~\\AppData\\Local\\Temp\\ipykernel_36452\\2484441159.py\u001b[0m in \u001b[0;36m?\u001b[1;34m()\u001b[0m\n\u001b[0;32m      8\u001b[0m \u001b[0m科目表\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'科目级别2'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0m科目表\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'科目级别'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mshift\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m-\u001b[0m\u001b[1;36m1\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      9\u001b[0m \u001b[0m科目表\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'是否末级'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0m科目表\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mapply\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;32mlambda\u001b[0m \u001b[0mrow\u001b[0m\u001b[1;33m:\u001b[0m \u001b[1;34m'否'\u001b[0m \u001b[1;32mif\u001b[0m \u001b[0mrow\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'科目级别2'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m>\u001b[0m \u001b[0mrow\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'科目级别'\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;32melse\u001b[0m \u001b[1;34m'是'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0maxis\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;36m1\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     10\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     11\u001b[0m \u001b[1;31m# 将数据写入到excel\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 12\u001b[1;33m \u001b[0mwb_tb\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msheets\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'部门收支表'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mrange\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'a2'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mvalue\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0m科目表\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mloc\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'科目编码'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'科目级别'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'科目名称'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'是否末级'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;32me:\\anaconda\\Lib\\site-packages\\xlwings\\main.py\u001b[0m in \u001b[0;36m?\u001b[1;34m(self, name_or_index)\u001b[0m\n\u001b[0;32m   5066\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0m__call__\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mname_or_index\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   5067\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mname_or_index\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mSheet\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   5068\u001b[0m             \u001b[1;32mreturn\u001b[0m \u001b[0mname_or_index\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   5069\u001b[0m         \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 5070\u001b[1;33m             \u001b[1;32mreturn\u001b[0m \u001b[0mSheet\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mimpl\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mimpl\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mname_or_index\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;32me:\\anaconda\\Lib\\site-packages\\xlwings\\_xlwindows.py\u001b[0m in \u001b[0;36m?\u001b[1;34m(self, name_or_index)\u001b[0m\n\u001b[0;32m    894\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0m__call__\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mname_or_index\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 895\u001b[1;33m         \u001b[1;32mreturn\u001b[0m \u001b[0mSheet\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mxl\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mxl\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mname_or_index\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;32me:\\anaconda\\Lib\\site-packages\\xlwings\\_xlwindows.py\u001b[0m in \u001b[0;36m?\u001b[1;34m(self, *args, **kwargs)\u001b[0m\n\u001b[0;32m    233\u001b[0m                 ) and e.hresult == -2147418111:\n\u001b[0;32m    234\u001b[0m                     \u001b[0mn_attempt\u001b[0m \u001b[1;33m+=\u001b[0m \u001b[1;36m1\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    235\u001b[0m                     \u001b[1;32mcontinue\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    236\u001b[0m                 \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 237\u001b[1;33m                     \u001b[1;32mraise\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    238\u001b[0m             \u001b[1;32mexcept\u001b[0m \u001b[0mAttributeError\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    239\u001b[0m                 \u001b[1;32mif\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0mN_COM_ATTEMPTS\u001b[0m \u001b[1;32mor\u001b[0m \u001b[0mn_attempt\u001b[0m \u001b[1;33m<\u001b[0m \u001b[0mN_COM_ATTEMPTS\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    240\u001b[0m                     \u001b[0mn_attempt\u001b[0m \u001b[1;33m+=\u001b[0m \u001b[1;36m1\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32me:\\anaconda\\Lib\\site-packages\\win32com\\gen_py\\00020813-0000-0000-C000-000000000046x0x1x9.py\u001b[0m in \u001b[0;36m?\u001b[1;34m(self, Index)\u001b[0m\n\u001b[0;32m  36703\u001b[0m         \u001b[1;32mdef\u001b[0m \u001b[0m__call__\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mIndex\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mdefaultNamedNotOptArg\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m> 36704\u001b[1;33m \t\tret = self._oleobj_.InvokeTypes(0, LCID, 2, (9, 0), ((12, 1),),Index\n\u001b[0m\u001b[0;32m  36705\u001b[0m \t\t\t)\n\u001b[0;32m  36706\u001b[0m                 \u001b[1;32mif\u001b[0m \u001b[0mret\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m  36707\u001b[0m                         \u001b[0mret\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mDispatch\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mret\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m'__call__'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mcom_error\u001b[0m: (-2147352567, '发生意外。', (0, None, None, None, 0, -2147352565), None)"
     ]
    }
   ],
   "source": [
    "# 取出来科目来，作为部门收支表的index。\n",
    "\n",
    "科目表=tb.loc[tb['科目编码'].notna(),['科目编码','科目名称']].query('科目编码 != None')\n",
    "科目表['科目级别']=科目表['科目编码'].str.strip().str.len().map({4.0:1,6.0:2,8.0:3})\n",
    "\n",
    "# 创建是否末级列\n",
    "# 将科目级别的值都上移一行，创建是否末级\n",
    "科目表['科目级别2']=科目表['科目级别'].shift(-1)\n",
    "科目表['是否末级']=科目表.apply(lambda row: '否' if row['科目级别2']> row['科目级别'] else '是',axis=1)\n",
    "\n",
    "# 将数据写入到excel\n",
    "wb_tb.sheets('部门收支表').range('a2').value=科目表.loc[:,['科目编码','科目级别','科目名称','是否末级']]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "### 这个是做 部门收支表时候用的，做资产负债表说明用不到，是一个拆科目列的东西\n",
    "\n",
    "tb3=tb2\n",
    "### 3.将辅助核算项从科目中拆分出来 \n",
    "\n",
    "# 取出原科目名称列中的辅助核算项\n",
    "tb3['部门']=tb3.apply(lambda row:row ['科目名称'] if pd.isnull(row['科目编码']) else '',axis=1).str.strip()\n",
    "\n",
    "# 取出原科目名称列中的科目名称\n",
    "tb3['科目名称2']=tb3.apply(lambda row:pd.NA if pd.isnull(row['科目编码']) else row ['科目名称'],axis=1)\n",
    "tb3['科目名称2'].fillna(method='ffill',inplace=True)  # 填充缺失值#"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
